package com.balloon.io;

import com.balloon.logic.City;
import com.balloon.logic.Country;
import java.sql.*;

/**
 * This class is used for handling database operation.
 * 
 */
public class CountryDao {

    static Connection currentCon = null;
    static ResultSet rs = null;
    static Statement stmt = null;

    /**
     * This method is used for getting country infromation from database
     * @param country a reference of country
     * @return the country which contains the country infromation
     */
    public static Country getCountryInformation(Country country) {
        // Preparing some objects for connection
        String countryName = country.getName();
        //Test whether it could get correctly.
        System.out.println("Selected country is " + countryName);

        int countryID = 0;
        try {
            //connect to DB
            currentCon = ConnectionManager.getConnection();
            stmt = currentCon.createStatement();

            //Query for countryID and longitude and latitude
            String selectQuery =
                    "select * from country where countryName='"
                    + countryName
                    + "'";
            rs = stmt.executeQuery(selectQuery);
            System.out.println(selectQuery);
            boolean more = rs.next();
            if (!more) {
                System.out.println("Error happened");
            } else if (more) {
                countryID = Integer.parseInt(rs.getString("countryID"));
                double locationX = Double.parseDouble(rs.getString("longitude"));
                double locationY = Double.parseDouble(rs.getString("latitude"));

                country.setLocationX(locationX);
                country.setLocationY(locationY);
            }

            /*selectQuery =
            "select * from countrycomment where countryID='"
            + countryID
            + "'";

            rs = stmt.executeQuery(selectQuery);
            while(rs.next()){
            String commentDescription=rs.getString("description");
            String personEmail=rs.getString("person");
            String postTime=rs.getString("postTime");

            country.addComments(personEmail+"says: "+commentDescription +"at " +postTime);
            }*/

            //Query for country rank
            selectQuery =
                    "select * from countryrank where countryID='"
                    + countryID
                    + "'";

            int rank = 0;
            int tempCounter = 0;
            rs = stmt.executeQuery(selectQuery);
            while (rs.next()) {
                rank += Integer.parseInt(rs.getString("rank"));
                tempCounter++;
            }

            country.setRank(rank / tempCounter);

            /*selectQuery =
            "select * from countrylink where countryID='"
            + countryID
            + "'";

            rs = stmt.executeQuery(selectQuery);
            while(rs.next()){
            String website=rs.getString("website");
            String description=rs.getString("description");
            }*/

            //Query for city information
            selectQuery =
                    "select * from city where countryID='"
                    + countryID
                    + "'";

            rs = stmt.executeQuery(selectQuery);

            String cityName = "";
            double cityLongitude = 0;
            double cityLatitude = 0;
            String cityDescription = "";
            while (rs.next()) {
                cityName = rs.getString("cityName");
                cityLongitude = Double.parseDouble(rs.getString("longitude"));
                cityLatitude = Double.parseDouble(rs.getString("latitude"));
                cityDescription = rs.getString("description");

                City city = new City(cityName, cityLongitude, cityLatitude, cityDescription);
                country.addCity(city);


            }

            //Query for country useful links
            selectQuery =
                    "select * from countrylink where countryID='"
                    + countryID
                    + "'";

            rs = stmt.executeQuery(selectQuery);

            String linkDescription = "";
            String linkWebsite = "";
            while (rs.next()) {
                linkDescription = rs.getString("description");
                linkWebsite = rs.getString("website");

                country.setCountryUsefulLinks(linkWebsite, linkDescription);
            }



        } catch (Exception ex) {
            System.out.println("Log In failed: An Exception has occurred! " + ex);
        } //Some exception handling
        finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {
                }
                rs = null;
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception e) {
                }
                stmt = null;
            }

            if (currentCon != null) {

                try {
                    currentCon.close();
                } catch (Exception e) {
                }
                currentCon = null;

            }
        }

        return country;
    }
}
